﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using VCN.App.DataTransferObjects;
using System.Data.SqlClient;

namespace VCN.App.DataAccessLayer
{
    public class CategoriesDAO : BasicDAO<Categories>
    {
        protected override DataTable exeSpSelect(string ip_storedProcedure)
        {
            return base.exeSpSelect("sp_tblCategories_SelectAll");//sp_tblCategories_SelectAll
        }
        public DataTable selectAll2Dt()
        {
            return this.exeSpSelect("");
        }
        protected  DataTable exeSpSelectById(string ip_storeProcedure, int ip_id)
        {
            return base.exeSpSelectById("sp_tblCategories_SelectRow", ip_id);
        }
        public Categories SelectById(int ip_id)
        {
            DataTable v_dt = this.exeSpSelectById("", ip_id);
            Categories v_Categories = new Categories();
            v_Categories.Id = ip_id;
            v_Categories.CategoryName = v_dt.Rows[0]["CATEGORY_NAME"].ToString();
            v_Categories.Image = v_dt.Rows[0]["ANH_MINH_HOA"].ToString();
            //foreach (DataRow v_dr in v_dt)
            //{
            //    v_Categories.CategoryName = v_dr[1].ToString();
            //}

            return v_Categories;
        }
        public DataTable searchCategories(string ip_TenCM)
        {
            SqlConnection v_conn = BasicDAO<Categories>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblCategories_Search", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = ip_TenCM;
            DataTable v_dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(v_command);
            try
            {

                adapter.Fill(v_dt);
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
            }
            return v_dt;
        }
        public override System.Collections.Generic.List<Categories> selectAll2List(Categories ip_obj)
        {
            throw new NotImplementedException();
        }
        //public override bool deleteByID(Categories ip_Categories)
        //{
        //    return base.deleteByID(ip_Categories);
        //}
        public override bool update(Categories ip_Categories)
        {
            System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
            string driver = (string)settingsReader.GetValue("ConnectionString", typeof(string));
            SqlConnection v_conn = new SqlConnection(driver);
            try
            {
                v_conn.Open();
            }
            catch (Exception exp)
            {
                HttpContext.Current.Response.Write(exp.ToString());
            }
            SqlCommand sqlcom = new SqlCommand("sp_tblCategories_Update", v_conn);
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.Parameters.Add("@Id", SqlDbType.Int).Value = ip_Categories.Id;
            sqlcom.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = ip_Categories.CategoryName;
            sqlcom.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_Categories.Image;
            try
            {
                sqlcom.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        public bool delete(Categories ip_Categories)
        {
            System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
            string driver = (string)settingsReader.GetValue("ConnectionString", typeof(string));
            SqlConnection v_conn = new SqlConnection(driver);
            try
            {
                v_conn.Open();
            }
            catch (Exception exp)
            {
                HttpContext.Current.Response.Write(exp.ToString());
            }
            SqlCommand sqlcom = new SqlCommand("sp_tblCategories_DeleteRow", v_conn);
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.Parameters.Add("@Id", SqlDbType.Int).Value = ip_Categories.Id;

            try
            {
                sqlcom.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }

        public override bool insert(Categories ip_Categories)
        {
            System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
            string driver = (string)settingsReader.GetValue("ConnectionString", typeof(string));
            SqlConnection v_conn = new SqlConnection(driver);
            try
            {
                v_conn.Open();
            }
            catch (Exception exp)
            {
                HttpContext.Current.Response.Write(exp.ToString());
            }
            SqlCommand sqlcom = new SqlCommand("sp_tblCategories_Insert", v_conn);
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = ip_Categories.CategoryName;
            sqlcom.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_Categories.Image;
            try
            {
                sqlcom.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
    }
}
